package com.example.backend.repository;

import com.example.backend.domain.entity.Ticket;
import com.example.backend.domain.enums.TicketStatus;
import com.example.backend.domain.enums.TicketPriority;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.Instant;
import java.util.List;
import java.util.Map;
import java.util.Optional;

/**
 * 优化的工单Repository
 * 避免N+1查询问题，提升查询性能
 */
@Repository
public interface OptimizedTicketRepository extends JpaRepository<Ticket, Long> {

    /**
     * 获取工单详情（带关联数据，避免N+1查询）
     */
    @Query("SELECT t FROM Ticket t " +
           "LEFT JOIN FETCH t.customer " +
           "LEFT JOIN FETCH t.assignedTo " +
           "LEFT JOIN FETCH t.category " +
           "LEFT JOIN FETCH t.createdBy " +
           "WHERE t.id = :id")
    Optional<Ticket> findByIdWithDetails(@Param("id") Long id);

    /**
     * 获取用户工单（带关联数据）
     */
    @Query("SELECT t FROM Ticket t " +
           "LEFT JOIN FETCH t.customer " +
           "LEFT JOIN FETCH t.assignedTo " +
           "LEFT JOIN FETCH t.category " +
           "WHERE t.customer.id = :userId")
    Page<Ticket> findByCustomerIdWithDetails(@Param("userId") Long userId, Pageable pageable);

    /**
     * 获取分配给用户的工单（带关联数据）
     */
    @Query("SELECT t FROM Ticket t " +
           "LEFT JOIN FETCH t.customer " +
           "LEFT JOIN FETCH t.assignedTo " +
           "LEFT JOIN FETCH t.category " +
           "WHERE t.assignedTo.id = :userId")
    Page<Ticket> findByAssignedToIdWithDetails(@Param("userId") Long userId, Pageable pageable);

    /**
     * 按条件查询工单（带关联数据）
     */
    @Query("SELECT t FROM Ticket t " +
           "LEFT JOIN FETCH t.customer " +
           "LEFT JOIN FETCH t.assignedTo " +
           "LEFT JOIN FETCH t.category " +
           "WHERE (:status IS NULL OR t.status = :status) " +
           "AND (:priority IS NULL OR t.priority = :priority) " +
           "AND (:assignedToId IS NULL OR t.assignedTo.id = :assignedToId) " +
           "AND (:customerId IS NULL OR t.customer.id = :customerId)")
    Page<Ticket> findByConditionsWithDetails(@Param("status") TicketStatus status,
                                           @Param("priority") TicketPriority priority,
                                           @Param("assignedToId") Long assignedToId,
                                           @Param("customerId") Long customerId,
                                           Pageable pageable);

    /**
     * 获取工单统计（使用原生SQL提升性能）
     */
    @Query(value = "SELECT " +
                   "COUNT(*) as total, " +
                   "SUM(CASE WHEN status = 'OPEN' THEN 1 ELSE 0 END) as open, " +
                   "SUM(CASE WHEN status = 'IN_PROGRESS' THEN 1 ELSE 0 END) as inProgress, " +
                   "SUM(CASE WHEN status = 'RESOLVED' THEN 1 ELSE 0 END) as resolved, " +
                   "SUM(CASE WHEN status = 'CLOSED' THEN 1 ELSE 0 END) as closed " +
                   "FROM tickets", nativeQuery = true)
    Map<String, Object> getTicketStatistics();

    /**
     * 获取用户工单统计
     */
    @Query(value = "SELECT " +
                   "COUNT(*) as total, " +
                   "SUM(CASE WHEN status = 'OPEN' THEN 1 ELSE 0 END) as open, " +
                   "SUM(CASE WHEN status = 'IN_PROGRESS' THEN 1 ELSE 0 END) as inProgress, " +
                   "SUM(CASE WHEN status = 'RESOLVED' THEN 1 ELSE 0 END) as resolved, " +
                   "SUM(CASE WHEN status = 'CLOSED' THEN 1 ELSE 0 END) as closed " +
                   "FROM tickets WHERE customer_id = :userId", nativeQuery = true)
    Map<String, Object> getTicketStatisticsByUserId(@Param("userId") Long userId);

    /**
     * 获取今日工单统计
     */
    @Query(value = "SELECT " +
                   "SUM(CASE WHEN DATE(created_at) = CURDATE() THEN 1 ELSE 0 END) as createdToday, " +
                   "SUM(CASE WHEN DATE(resolved_at) = CURDATE() THEN 1 ELSE 0 END) as resolvedToday " +
                   "FROM tickets", nativeQuery = true)
    Map<String, Object> getTodayTicketStatistics();

    /**
     * 获取工单趋势数据（最近7天）
     */
    @Query(value = "SELECT DATE(created_at) as date, COUNT(*) as count " +
                   "FROM tickets " +
                   "WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) " +
                   "GROUP BY DATE(created_at) " +
                   "ORDER BY date", nativeQuery = true)
    List<Map<String, Object>> getTicketTrendData();

    /**
     * 获取工单按优先级分布
     */
    @Query(value = "SELECT priority, COUNT(*) as count " +
                   "FROM tickets " +
                   "GROUP BY priority", nativeQuery = true)
    List<Map<String, Object>> getTicketPriorityDistribution();

    /**
     * 获取工单按状态分布
     */
    @Query(value = "SELECT status, COUNT(*) as count " +
                   "FROM tickets " +
                   "GROUP BY status", nativeQuery = true)
    List<Map<String, Object>> getTicketStatusDistribution();

    /**
     * 获取平均解决时间
     */
    @Query(value = "SELECT AVG(TIMESTAMPDIFF(HOUR, created_at, resolved_at)) as avgResolutionHours " +
                   "FROM tickets " +
                   "WHERE status = 'RESOLVED' " +
                   "AND resolved_at IS NOT NULL", nativeQuery = true)
    Optional<Double> getAverageResolutionTime();

    /**
     * 获取SLA合规率
     */
    @Query(value = "SELECT " +
                   "COUNT(*) as total, " +
                   "SUM(CASE WHEN TIMESTAMPDIFF(HOUR, created_at, COALESCE(resolved_at, NOW())) <= :slaHours THEN 1 ELSE 0 END) as compliant " +
                   "FROM tickets " +
                   "WHERE status IN ('RESOLVED', 'CLOSED')", nativeQuery = true)
    Map<String, Object> getSlaComplianceRate(@Param("slaHours") int slaHours);

    /**
     * 获取即将到期的工单
     */
    @Query("SELECT t FROM Ticket t " +
           "LEFT JOIN FETCH t.customer " +
           "LEFT JOIN FETCH t.assignedTo " +
           "WHERE t.status IN ('OPEN', 'IN_PROGRESS') " +
           "AND t.dueDate IS NOT NULL " +
           "AND t.dueDate <= :dueTime " +
           "ORDER BY t.dueDate ASC")
    List<Ticket> findUpcomingDueTickets(@Param("dueTime") Instant dueTime);

    /**
     * 获取超时工单
     */
    @Query("SELECT t FROM Ticket t " +
           "LEFT JOIN FETCH t.customer " +
           "LEFT JOIN FETCH t.assignedTo " +
           "WHERE t.status IN ('OPEN', 'IN_PROGRESS') " +
           "AND (t.dueDate IS NULL OR t.dueDate < :cutoffTime) " +
           "ORDER BY t.createdAt ASC")
    Page<Ticket> findOverdueTickets(@Param("cutoffTime") Instant cutoffTime, Pageable pageable);

    /**
     * 批量更新工单状态
     */
    @Query("UPDATE Ticket t SET t.status = :status, t.updatedAt = :updatedAt WHERE t.id IN :ids")
    int updateTicketStatusBatch(@Param("ids") List<Long> ids, 
                               @Param("status") TicketStatus status, 
                               @Param("updatedAt") Instant updatedAt);

    /**
     * 获取用户最近创建的工单
     */
    @Query("SELECT t FROM Ticket t " +
           "LEFT JOIN FETCH t.customer " +
           "WHERE t.customer.id = :userId " +
           "ORDER BY t.createdAt DESC")
    List<Ticket> findRecentTicketsByUserId(@Param("userId") Long userId, Pageable pageable);

    /**
     * 检查用户是否有未完成的工单
     */
    @Query("SELECT COUNT(t) > 0 FROM Ticket t " +
           "WHERE t.customer.id = :userId " +
           "AND t.status IN ('OPEN', 'IN_PROGRESS')")
    boolean hasUnfinishedTickets(@Param("userId") Long userId);
}
